#!/usr/bin/env python
# coding=utf-8

# File Name: pushData.py
# Author: Bill Zhang
# Mail: zchcandid@gmail.com 
# Created Time: Mon 28 Oct 2013 11:50:59 AM CST
import urllib.request  
import hashlib
import json 
import time
#mysql connector
import mysql.connector
from mysql.connector import errorcode
def Run(times,interval):
    password="ocs~rsh,uBZN"
    SessionKey=getSession(password)
    pushURL='http://192.168.100.200:9998/SuppliersItem/Update/json/'+SessionKey

    #Fetch the data from mysql
    #get the cursor to manipulate the mysql
    db=MysqlConnect('mouser')
    Mydb1=db.cursor(buffered=True)
    Mydb2=db.cursor(buffered=True)
    #query the data
    #Interval value of each pushing
    PrePush=''
    Query="SELECT id,cat_id,goods_sn,goods_name,provider_name,goods_number,shop_price,goods_desc FROM ecs_goods WHERE id BETWEEN "+str((times-1)*interval)+' AND '+str(times*interval)
    Mydb1.execute(Query)
    #get the data from mysql and push into json

    for oits in Mydb1:
    #The data of OITS Tables
        #checke the oits
        if not oits:
            continue
        #escape sequence
        IDesc=(str(oits[7]).replace("\n",""))
        IDesc=IDesc.replace("'","&#039")
        #Encap and Pachage:
       # EncapQuery="SELECT ext_value FROM ecs_goods_attr_fields_"+str(oits[0]%10)+" WHERE goods_id="+str(oits[0])+" AND ext_name LIKE '%封装%'"
       # Mydb2.execute(EncapQuery)
       # Encap=Mydb2.fetchone()
       # if(Encap):
       #     Encap=str(Encap[0]).replace('"','\"')
       #     Encap=str(Encap[0]).replace("'",'\'')
       # else
        Encap="-"
            
       # PackageQuery="SELECT ext_value FROM ecs_goods_attr_fields_"+str(oits[0]%10)+" WHERE goods_id="+str(oits[0])+" AND ext_name LIKE '%包装%'"
       # Mydb2.execute(PackageQuery)
       # Package=Mydb2.fetchone()
       # if(Package):
       #     Package=str(Package[0]).replace('"','\'')
       #     Package=str(Package[0]).replace("'",'\'')
       # else:
        Package="-"
        oitsData={ "HQID":oits[0],"SupplierCategory":oits[1],"SuppliersItemCode":oits[2],"ModelCode":oits[3],"BrandName":oits[4],"Quantity":oits[5],"Price":float(oits[6]),"ItemDesc":IDesc,"CardName":"Mouser","Encap":Encap,"Package":Package,"ShowPrice":"true","PriceParity":"true","Rohs":"true"}
        oitsData=str(oitsData).replace('}',',')
        oitsData=oitsData.replace('"','\\"')
        oitsData=oitsData.replace("&#039","\\'")
    #The data of OITS1 Tables
        Ftable="ecs_goods_price_"+str(oits[0]%10)
        Query2="SELECT purchases,unit_price FROM "+Ftable+" WHERE goods_id ="+str(oits[0])
        #limit2=Ftable
        Mydb2.execute(Query2)
        MOQ=1
        i=1
        oits1=Mydb2.fetchone()
        if oits1 is not None:
            Items='"Items":['
            while oits1 is not None:
                Item={"LineNum":i,"Quantity":oits1[0],"Price":float(oits1[1])}
                if(i!=1):
                    Items =Items+","
                if(i==1):
                    MOQ=oits1[0]
                Items=Items+str(Item)
                i=i+1
                oits1=Mydb2.fetchone()
            #   print(Item)
            Items=Items+'],"MOQ":'+str(MOQ)+'},'
        else:
           # NoPrice=NoPrice+1
           # Items=',"MOQ":'+str(MOQ)+'},'
           continue
        PrePush=PrePush+oitsData+Items
    pushData='['+PrePush+']'
    LoadTime=time.strftime('%H:%M:%S',time.localtime(time.time()))
    L="Load json end time:"+str(LoadTime)
    print(L)

    pushData=pushData.replace("'",'"')

#    fileHandle = open ( 'jsonData.txt', 'a'  ) 
#    fileHandle.write(pushData) 
#    fileHandle.close() 
    pushing(pushURL,pushData,times)
    #close the MysqlConnect
    Mydb1.close()
    Mydb2.close()
    db.close()
    if(oits[0]>=337940):
        return 0
    return 1
 
#get the SessionKey
def getSession(password):
    url="http://192.168.100.200:9998/Security/RegisterUser/json/T0030" 
    pwd=password  
    m = hashlib.md5(pwd.encode(encoding='utf-8'))  
    PassKey=m.hexdigest()
    #print(m.hexdigest())  
    #print(PassKey)
    #data=urllib.parse.urlencode(password) 

    PassKey=PassKey.encode(encoding='utf-8', errors = 'strict')
    #创建请求对象  
    req=urllib.request.Request(url,PassKey) 
    #获得服务器返回的数据  
    response=urllib.request.urlopen(req) 
    sessionKey=response.read().decode('utf-8')
    SesKey=json.loads(sessionKey)
   # print(SesKey)
    #print(SesKey["Result"])
    return SesKey["Result"]

#Manuplate the Database
def MysqlConnect(DataBase):
    try:
          cnx = mysql.connector.connect(user='root', password='123456',host='192.168.100.201',database=DataBase)
          #cnx = mysql.connector.connect(user='admin001', password='admin001',host='192.168.100.58',database=DataBase)
    except mysql.connector.Error as err:
          if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
              print("Something is wrong with your user name or password")
          elif err.errno == errorcode.ER_BAD_DB_ERROR:
              print("Database does not exists")
          else:
              print(err)
    else:
        return cnx 
def pushing(URL,PushData,times):
    PushData=PushData.encode(encoding='utf-8', errors = 'strict')
    #创建请求对象  
    req1=urllib.request.Request(URL,PushData) 
    #获得服务器返回的数据  
    response1=urllib.request.urlopen(req1) 
    id=response1.read().decode('utf-8') 
    #print(id)
    id=id.replace('"','&quot')
    id=id.replace("'",'&#039')
    #print(id)
    #connect mysql for recording the log
    db1=MysqlConnect('pushLog')
    log=db1.cursor()
    logQuery="Insert into mouser(LOG,times) VALUES('"+id+"',"+str(times)+")"
    log.execute(logQuery)
    log.close()
    db1.close()
